{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、数据获取\n",
    "数据获取隐含对数据的存储提前规划，对数据进行必要清洗和规整等，为进一步对数据分析和处理做好准备。一般情况下数据的来源有：\n",
    "- 本地文件：读取本地各种格式的文件，python的标准库和第三方包中有大量的api，主要的格式有文本文件，Excel文件和二进制文件等\n",
    "- 数据库：所有编程语言都具有读取数据库的能力，python的标准库和第三方包中有大量的api访问目前主流的数据，这包括关系型数据库sqlite，mysql和非关系型数据库mongodb等。\n",
    "- 网络：从网络获取数据，俗称爬虫，利用编程模仿浏览器获取互联网数据。理论上通过爬虫可以获取互联网上允许访问的任何数据，包括文本（html，xml, json等），二进制的文件、音频和视频等。\n",
    "\n",
    "利用python获取数据能力的提高，不仅仅来自与python编程能力的提高，还包括对各领域知识的掌握。例如爬虫需要了解web领域中的http协议的规则，访问关系型数据需要了解sql等。下面我们分别针对以上数据来源进行实战，打下基础。\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、读写本地文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 使用python标准库"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 读写文本文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "'utf-8'"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys\n",
    "sys.getdefaultencoding()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'data/ch04/hello.txt'",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mFileNotFoundError\u001B[0m                         Traceback (most recent call last)",
      "\u001B[1;32m<ipython-input-3-8b303620ec6a>\u001B[0m in \u001B[0;36m<module>\u001B[1;34m\u001B[0m\n\u001B[1;32m----> 1\u001B[1;33m \u001B[0mf\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mopen\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m'data/ch04/hello.txt'\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;34m'r'\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mencoding\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;34m'utf-8'\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m      2\u001B[0m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mf\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mread\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m      3\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;31mFileNotFoundError\u001B[0m: [Errno 2] No such file or directory: 'data/ch04/hello.txt'"
     ]
    }
   ],
   "source": [
    "f = open('data/ch04/hello.txt', 'r', encoding='utf-8')\n",
    "print(f.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "python标准库中的open函数可以创建一个文件对象，通过文件对象可以对文件进行各种操作。第2个参数是文件打开模式，\n",
    "- r\t只读\n",
    "- rb 二进制格式只读\n",
    "- r+ 读写\n",
    "- rb+ 二进制格式读写\n",
    "- w\t写，文件已存在则覆盖，文件不存在则创建\n",
    "- wb 二进制格式写，文件已存在则覆盖，文件不存在则创建\n",
    "- w+ 读写，文件已存在则覆盖，文件不存在则创建\n",
    "- wb+ 二进制读写，文件已存在则覆盖，文件不存在则创建\n",
    "- a 追加\n",
    "- ab 二进制格式追加\n",
    "- a+ 读写追加\n",
    "- ab+ 二进制格式读写追加\n",
    "\n",
    "文件对象是一个可迭代对象，可以一行行读出也可以循环读出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hello world!\n",
      "\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "f.seek(0) #回到内容开始位置\n",
    "print(f.readline())\n",
    "print(f.readline())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hello world!\n",
      "\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "\n"
     ]
    }
   ],
   "source": [
    "f.seek(0)\n",
    "for line in f:\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "完成对文件的操作后，调用close方法关闭文件对象，释放系统资源，特别是读取大文件，占用大量内存。程序复杂时，关闭前注意检查文件对象是否已经被回收，python提供with语句自动调用close方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    f = open('data/ch04/hello.txt', 'r', encoding='utf-8')\n",
    "    print(f.read())\n",
    "finally:\n",
    "    if f:\n",
    "        f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open('data/ch04/hello.txt', 'r', encoding='utf-8') as f:\n",
    "    print(f.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "也可以写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "That is right.\n",
      "﻿Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "That is right.\n",
      "﻿Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "That is right.\n",
      "Hello world!\n",
      "A hello you may never have expected but that may just be the one you need.\n",
      "一个你从来没有预期的‘你好’却往往可能是你真真需要的。\n",
      "That is right.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open('data/ch04/hello.txt', 'r', encoding='utf-8') as f:\n",
    "    lines = f.readlines()    \n",
    "    with open('data/ch04/world.txt', 'a', encoding='utf-8') as f2:\n",
    "        f2.writelines(lines)\n",
    "        f2.write('That is right.\\n')\n",
    "\n",
    "with open('data/ch04/world.txt', 'r', encoding='utf-8') as f3:\n",
    "    print(f3.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：将python重复10次写入文本文件，并读出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "python\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open('data/ch04/temp.txt', 'w', encoding='utf-8') as f4:        \n",
    "    f4.write('python\\n' * 10)\n",
    "\n",
    "with open('data/ch04/temp.txt', 'r', encoding='utf-8') as f5:\n",
    "    print(f5.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 处理json数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    " \"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\n",
    " \"pet\": null,\n",
    " \"siblings\": [{\"name\": \"Scott\", \"age\": 25, \"pet\": \"Zuko\"},\n",
    "              {\"name\": \"Katie\", \"age\": 33, \"pet\": \"Cisco\"}]\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Wes',\n",
       " 'pet': None,\n",
       " 'places_lived': ['United States', 'Spain', 'Germany'],\n",
       " 'siblings': [{'age': 25, 'name': 'Scott', 'pet': 'Zuko'},\n",
       "  {'age': 33, 'name': 'Katie', 'pet': 'Cisco'}]}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "result = json.loads(obj)#loads方法实现字符串对象转换成JSON对象\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"name\": \"Wes\", \"places_lived\": [\"United States\", \"Spain\", \"Germany\"], \"pet\": null, \"siblings\": [{\"name\": \"Scott\", \"age\": 25, \"pet\": \"Zuko\"}, {\"name\": \"Katie\", \"age\": 33, \"pet\": \"Cisco\"}]}'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asjson = json.dumps(result)#dumps()方法实现JSON对象转换成字符串对象\n",
    "asjson"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果要处理的是文件而不是字符串，可以使用 json.dump() 和 json.load() 来编码和解码JSON数据\n",
    "\n",
    "在文件写入下有时候需要编程实现传参，可以用json.dump(target,key=value)传参\n",
    "\n",
    "具体：汉字编码，ensure_ascii=False 实现中文的输出，否则会输出Unicode的字符如\\u7357"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Wes',\n",
       " 'pet': None,\n",
       " 'places_lived': ['United States', 'Spain', 'Germany'],\n",
       " 'siblings': [{'age': 25, 'name': 'Scott', 'pet': 'Zuko'},\n",
       "  {'age': 33, 'name': 'Katie', 'pet': 'Cisco'}]}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('data/ch04/data.json', 'w', encoding='utf-8') as f:\n",
    "    json.dump(obj, f)\n",
    "\n",
    "with open('data/ch04/data.json', 'r', encoding='utf-8') as f:\n",
    "    result_str = json.load(f)\n",
    "    result =  json.loads(result_str)\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 保存python对象\n",
    "还可以使用shelve模拟一个key-value数据库。shelve可以将python对象直接保存到文件中，取出时还是一个python对象，不需要像传统数据库一样，先取出数据，然后重构对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter command:quit\n"
     ]
    }
   ],
   "source": [
    "import sys, shelve\n",
    "\n",
    "def storeperson(db):\n",
    "    id = input('Id:')\n",
    "    person = {}\n",
    "    person['name'] = input('Name:')\n",
    "    person['age'] = input('Age:')\n",
    "    person['mobile'] = input('Mobile:')\n",
    "    db[id] = person\n",
    "\n",
    "def findperson(db):\n",
    "    id = input('Find by id:')\n",
    "    field = input('Field：')\n",
    "    field = field.strip().lower()\n",
    "    print(field.capitalize() + ':' + db[id][field])\n",
    "\n",
    "def entercommand():\n",
    "    cmd = input('Enter command:')\n",
    "    cmd = cmd.strip().lower()\n",
    "    return cmd\n",
    "\n",
    "def main():\n",
    "    db = shelve.open('data\\ch04\\store.dat')\n",
    "    try:\n",
    "        while True:\n",
    "            cmd = entercommand()\n",
    "            if cmd == 'store':\n",
    "                storeperson(db)\n",
    "            elif cmd == 'find':\n",
    "                findperson(db)\n",
    "            elif cmd == \"quit\":\n",
    "                return\n",
    "    finally:\n",
    "        db.close()\n",
    "\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 操作文件系统\n",
    "python标准库中还提供了对文件系统的操作，主要在os模块中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "e:\\Project\\Python\\course-begining-python\n",
      "e:\\Project\\Python\\course-begining-python\\files\n",
      "('e:\\\\Project\\\\Python\\\\course-begining-python', 'file.txt')\n",
      "('e:\\\\Project\\\\Python\\\\course-begining-python\\\\file', '.txt')\n",
      "['1. python课程简介.ipynb', '2. python开发环境和jupyter notebook.ipynb', '3. python编程基础.ipynb', '4. python数据获取.ipynb', 'python matplotlib可视化.ipynb', 'python numpy包.ipynb', 'python pandas数据处理.ipynb', 'python数据可视化.ipynb', 'python爬虫.ipynb', 'python编程进阶.ipynb']\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "path = os.path.abspath('.')\n",
    "#path = os.path.getcwd()\n",
    "print(path)\n",
    "path2 = os.path.join(path, 'files')\n",
    "print(path2)\n",
    "\n",
    "os.mkdir(path2)\n",
    "os.rmdir(path2)\n",
    "\n",
    "path3 = os.path.join(path, 'file.txt')\n",
    "print(os.path.split(path3))\n",
    "print(os.path.splitext(path3))\n",
    "\n",
    "print([x for x in os.listdir('.') if os.path.isfile(x) and os.path.splitext(x)[1]=='.ipynb'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：编写复制文件函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The file exists.\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import os\n",
    "\n",
    "def copyfile(from_filename, to_filename):\n",
    "    if os.path.exists(to_filename):\n",
    "        print('The file exists.')\n",
    "        return\n",
    "    print('Copy from %s to %s' % (from_filename, to_filename))\n",
    "    from_file = open(from_filename, encoding='utf-8')\n",
    "    data = from_file.read()\n",
    "    print('The file size is %d', len(data))\n",
    "    from_file.close      \n",
    "    to_file = open(to_filename,'w', encoding='utf-8')\n",
    "    to_file.write(data)\n",
    "    to_file.close\n",
    "    \n",
    "copyfile('data/ch04/hello.txt', 'data/ch04/copy.txt')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 使用numpy和pandas\n",
    "numpy能够读写磁盘上的文本数据和二进制数据，pandas可以读取文本和excel数据。\n",
    "#### 读写numpy数组至二进制文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "arr = np.arange(10)\n",
    "np.save('data/ch04/array', arr) #以.npy为扩展名，未压缩保存数组为二进制文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.load('data/ch04/array.npy')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "np.savez('data/ch04/array_archive.npz', a=arr, b=arr) #以.npz为扩展名，压缩保存多个数组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arch = np.load('data/ch04/array_archive.npz')\n",
    "arch['b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 读写numpy数组至文本文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "arr = np.random.randn(3, 4)\n",
    "np.savetxt('data/ch04/array_numpy.txt', arr, delimiter=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 1.36918849, -1.56213207,  0.00207497,  0.6651571 ],\n",
       "       [-0.02509497,  1.52177529, -0.69761807,  0.07524579],\n",
       "       [-0.19498267, -0.04730856,  0.01545124, -0.96100121]])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr = np.loadtxt('data/ch04/array_numpy.txt', delimiter=',')\n",
    "arr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pandas读写文本文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "!cat data/ch04/data.csv\n",
    "#!type data\\ch04\\data.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas在读取文本文件时会对数据进行一些处理，这包括建立索引，推断数据类型并转换，解析日期，对大文件迭代，跳过一些行，页脚和注释等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  a   b   c   d message\n",
       "0           0  1   2   3   4   hello\n",
       "1           1  5   6   7   8   world\n",
       "2           2  9  10  11  12     foo"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  a   b   c   d message\n",
       "0           0  1   2   3   4   hello\n",
       "1           1  5   6   7   8   world\n",
       "2           2  9  10  11  12     foo"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/ch04/data.csv', sep=',') #指定分隔符"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/ch04/data.csv')\n",
    "df.to_csv('data/ch04/data.csv') #写入文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randn(4000).reshape(1000, 4), columns=list('abcd'))\n",
    "df.ix[df.a > 0] = 1\n",
    "df.ix[df.a <= 0] = 0\n",
    "df.to_csv('data/ch04/data_random.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pandas读写excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>hello</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>world</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>foo</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      a     b     c     d message     1\n",
       "0   1.0   2.0   3.0   4.0   hello   NaN\n",
       "1  50.0   6.0   7.0   8.0   world   NaN\n",
       "2   9.0  10.0  11.0  12.0     foo   NaN\n",
       "a   NaN   NaN   NaN   NaN     NaN  50.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xls = pd.ExcelFile('data/ch04/data.xlsx')\n",
    "table = xls.parse('Sheet1')\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.loc['a', 1] = 50\n",
    "out = pd.ExcelWriter('data/ch04/data.xlsx')\n",
    "table.to_excel(out)\n",
    "out.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pandas读数据时的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0   1   2   3      4\n",
       "0  1   2   3   4  hello\n",
       "1  5   6   7   8  world\n",
       "2  9  10  11  12    foo"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_without_header.csv', header=None) #没有列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_without_header.csv', names=['a', 'b', 'c', 'd', 'message']) #指定列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>message</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>hello</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>world</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         a   b   c   d\n",
       "message               \n",
       "hello    1   2   3   4\n",
       "world    5   6   7   8\n",
       "foo      9  10  11  12"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = ['a', 'b', 'c', 'd', 'message']\n",
    "pd.read_csv('data/ch04/data_without_header.csv', names=names, index_col='message') #指定索引列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['            A         B         C\\n',\n",
       " 'aaa -0.264438 -1.026059 -0.619500\\n',\n",
       " 'bbb  0.927272  0.302904 -0.032399\\n',\n",
       " 'ccc -0.264273 -0.386314 -0.217601\\n',\n",
       " 'ddd -0.871858 -0.348382  1.100491']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open('data/ch04/data_abnormal.csv', encoding='utf-8'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaa</th>\n",
       "      <td>-0.264438</td>\n",
       "      <td>-1.026059</td>\n",
       "      <td>-0.619500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bbb</th>\n",
       "      <td>0.927272</td>\n",
       "      <td>0.302904</td>\n",
       "      <td>-0.032399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ccc</th>\n",
       "      <td>-0.264273</td>\n",
       "      <td>-0.386314</td>\n",
       "      <td>-0.217601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddd</th>\n",
       "      <td>-0.871858</td>\n",
       "      <td>-0.348382</td>\n",
       "      <td>1.100491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            A         B         C\n",
       "aaa -0.264438 -1.026059 -0.619500\n",
       "bbb  0.927272  0.302904 -0.032399\n",
       "ccc -0.264273 -0.386314 -0.217601\n",
       "ddd -0.871858 -0.348382  1.100491"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/ch04/data_abnormal.csv', sep='\\s+') #分隔符支持正则表达式，列名比数据行中的数量少，第一列被推断为索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bbb</th>\n",
       "      <td>0.927272</td>\n",
       "      <td>0.302904</td>\n",
       "      <td>-0.032399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddd</th>\n",
       "      <td>-0.871858</td>\n",
       "      <td>-0.348382</td>\n",
       "      <td>1.100491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            A         B         C\n",
       "bbb  0.927272  0.302904 -0.032399\n",
       "ddd -0.871858 -0.348382  1.100491"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/ch04/data_abnormal.csv', sep='\\s+', skiprows=[1, 3]) #跳过一些数据行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['something,a,b,c,d,message\\n',\n",
       " 'one,1,2,3,4,NA\\n',\n",
       " 'two,5,6,,8,world\\n',\n",
       " 'three,9,10,11,12,foo']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open('data/ch04/data_lost.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       two  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     foo"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_lost.csv') #推断NA、null和空缺"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       two  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     NaN"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_lost.csv', na_values=['foo']) #指定表示空缺的字符串"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       NaN  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     NaN"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_lost.csv', na_values={'message': ['foo'], 'something': ['two']}) #为不同列指定表示空缺的字符串"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pandas分块读取文本文件\n",
    "只想读取文件的一小部分或希望对文件进行迭代时"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.467976</td>\n",
       "      <td>-0.038649</td>\n",
       "      <td>-0.295344</td>\n",
       "      <td>-1.824726</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.358893</td>\n",
       "      <td>1.404453</td>\n",
       "      <td>0.704965</td>\n",
       "      <td>-0.200638</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.501840</td>\n",
       "      <td>0.659254</td>\n",
       "      <td>-0.421691</td>\n",
       "      <td>-0.057688</td>\n",
       "      <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.204886</td>\n",
       "      <td>1.074134</td>\n",
       "      <td>1.388361</td>\n",
       "      <td>-0.982404</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.354628</td>\n",
       "      <td>-0.133116</td>\n",
       "      <td>0.283763</td>\n",
       "      <td>-0.837063</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        one       two     three      four key\n",
       "0  0.467976 -0.038649 -0.295344 -1.824726   L\n",
       "1 -0.358893  1.404453  0.704965 -0.200638   B\n",
       "2 -0.501840  0.659254 -0.421691 -0.057688   G\n",
       "3  0.204886  1.074134  1.388361 -0.982404   R\n",
       "4  0.354628 -0.133116  0.283763 -0.837063   Q"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/ch04/data_large.csv', nrows=5) #只读取前五行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "E    368.0\n",
       "X    364.0\n",
       "L    346.0\n",
       "O    343.0\n",
       "Q    340.0\n",
       "M    338.0\n",
       "J    337.0\n",
       "F    335.0\n",
       "K    334.0\n",
       "H    330.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunker = pd.read_csv('data/ch04/data_large.csv', chunksize=1000) #设置分块大小，返回TextParser可迭代对象\n",
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(), fill_value=0)  #根据key列分组计数\n",
    "\n",
    "tot = tot.sort_values(ascending=False)\n",
    "tot[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：使用pandas对data/ch04/data*.csv进行数据处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、读写数据库\n",
    "小的应用可以将数据存储在文件中，但是对于大中型应用则需要成熟的数据库系统的支持。目前主流的数据库系统一般分为关系型数据库和非关系型数据库，关系型数据库包括sqlite和mysql等，非关系型数据库包括mongodb和redis等，接下里我们来讨论python对这些数据库的访问能力，以获取数据库中的数据。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 1. SQL\n",
    "SQL是访问关系型数据的的标准语言，可以使用SQL语句操作诸如sqlite、mysql、oralce和SQL Server等关系型数据库。所有的关系型数据库都是库表结构\n",
    "实现对数据增删改查基本SQL语句如下："
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "insert into table_name (column1,column2,column3,...) values (value1,value2,value3,...)\n",
    "delete from table_name where some_column=some_value;\n",
    "update table_name set column1=value1,column2=value2,... where some_column=some_value;\n",
    "select column_name,column_name from table_name where some_column=some_value order by column_name asc|desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "实现对库表结构的增删改基本SQL语句如下（注意不同关系型数据库产品可能会略有不同）："
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "create database dbname;\n",
    "drop database dbname;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "create table table_name\n",
    "(\n",
    "column_name1 data_type(size),\n",
    "column_name2 data_type(size),\n",
    "....\n",
    ");\n",
    "alter table table_name add column_name datatype\n",
    "alter table table_name drop column column_name\n",
    "alter table table_name alter column column_name datatype\n",
    "drop table table_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 访问sqlite\n",
    "sqlite是一个非常轻量级的关系型数据库，广泛应用于移动应用开发中。sqlite没有服务器端，通过api即可完成sql语句操作，sqlite将数据存储在一个本地文件中，甚至存储在内存中。python标准库中带有对sqlite访问的api，该api遵循python DB-API规范。\n",
    "![](images/sqlite.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "#conn = sqlite3.connect(':memory:') #数据存储在内存中\n",
    "conn = sqlite3.connect('data/ch04/sqlite.db') #数据存储在文件中\n",
    "sql=\"\"\"\n",
    "create table test\n",
    "(\n",
    "a varchar(20),\n",
    "b varchar(20),\n",
    "c real,\n",
    "d integer\n",
    ")\n",
    "\"\"\"\n",
    "conn.execute(\"drop table if exists test\")\n",
    "conn.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into test (a, b, c, d) values ('Fujian', 'Xiamen', 1.25, 6)\"\n",
    "conn.execute(sql)\n",
    "conn.commit()\n",
    "data = [('Zhejiang', 'Hangzhou', 2.6, 3), \n",
    "        ('Guangdong', 'Shenzhen', 1.7, 5)]\n",
    "sql = \"insert into test values (? ,?, ?, ?)\"\n",
    "conn.executemany(sql, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Fujian', 'Xiamen', 1.25, 6),\n",
       " ('Zhejiang', 'Hangzhou', 2.6, 3),\n",
       " ('Guangdong', 'Shenzhen', 1.7, 5)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = conn.execute(\"select * from test\")\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('a', None, None, None, None, None, None),\n",
       " ('b', None, None, None, None, None, None),\n",
       " ('c', None, None, None, None, None, None),\n",
       " ('d', None, None, None, None, None, None))"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.description #游标description属性包含列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fujian</td>\n",
       "      <td>Xiamen</td>\n",
       "      <td>1.25</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zhejiang</td>\n",
       "      <td>Hangzhou</td>\n",
       "      <td>2.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>1.70</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           a         b     c  d\n",
       "0     Fujian    Xiamen  1.25  6\n",
       "1   Zhejiang  Hangzhou  2.60  3\n",
       "2  Guangdong  Shenzhen  1.70  5"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(rows, columns=list(zip(*cursor.description))[0]) #数据直接传入DataFrame构造器，注意python 3中zip函数返回值发生变化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：尝试连接sqlite数据库并操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 访问mysql\n",
    "![](images/mysql.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = pymysql.connect(host='172.16.8.181', port=3306, user='root', password='wisesoe')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('drop database if exists test')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('create database test')\n",
    "conn.select_db('test')\n",
    "sql=\"\"\"\n",
    "create table test\n",
    "(\n",
    "a varchar(20),\n",
    "b varchar(20),\n",
    "c float,\n",
    "d int\n",
    ")\n",
    "\"\"\"\n",
    "cursor.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sql = \"insert into test (a, b, c, d) values ('Fujian', 'Xiamen', 1.25, 6)\"\n",
    "cursor.execute(sql)\n",
    "conn.commit()\n",
    "data = [('Zhejiang', 'Hangzhou', 2.6, 3), \n",
    "        ('Guangdong', 'Shenzhen', 1.7, 5)]\n",
    "sql = \"insert into test values (%s ,%s, %s, %s)\"\n",
    "cursor.executemany(sql, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = cursor.execute(\"select * from test\")\n",
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('Fujian', 'Xiamen', 1.25, 6),\n",
       " ('Zhejiang', 'Hangzhou', 2.6, 3),\n",
       " ('Guangdong', 'Shenzhen', 1.7, 5))"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = cursor.fetchall()\n",
    "rows #返回的是元组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fujian</td>\n",
       "      <td>Xiamen</td>\n",
       "      <td>1.25</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zhejiang</td>\n",
       "      <td>Hangzhou</td>\n",
       "      <td>2.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>1.70</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           a         b     c  d\n",
       "0     Fujian    Xiamen  1.25  6\n",
       "1   Zhejiang  Hangzhou  2.60  3\n",
       "2  Guangdong  Shenzhen  1.70  5"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(list(rows), columns=list(zip(*cursor.description))[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：尝试连接mysql数据库并操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 访问mongodb\n",
    "![](images/mongodb.png?20170619)\n",
    "mongodb与前面的的关系型数据库不同，是所谓NoSQL数据库中的一员。本质上mongodb是一个文档数据库，其内部没有库表结构，取而代之是库和集合，集合内不一定存储相同结构的数据。\n",
    "![](images/mongodb-collection.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "mongodb增删改查基本语法如下：\n",
    "![](images/mongodb-insert.png)\n",
    "![](images/mongodb-remove.png)\n",
    "![](images/mongodb-update.png)\n",
    "![](images/mongodb-find.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymongo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "client = pymongo.MongoClient('172.16.8.181', 27017)\n",
    "client.admin.authenticate('root', 'wisesoe', source='admin')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一般使用数据库连接字符串的方式："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x1ef905c9480>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = pymongo.MongoClient('mongodb://root:wisesoe@172.16.8.181/admin')\n",
    "db = client.test #指定库\n",
    "db.data.drop()\n",
    "db.data.insert_one({'a': 'Fujian', 'b': 'Xiamen', 'c': 1.25, 'd': 6})\n",
    "data = [{'a': 'Zhejiang', 'b': 'Hangzhou', 'c': 2.6, 'd': 3}, \n",
    "        {'a': 'Guangdong', 'b': 'Shenzhen', 'c': 1.7, 'd': 5}]\n",
    "db.data.insert_many(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['blog', 'system.indexes', 'data']"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.collection_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': ObjectId('595c528feaa295c58ad656ad'),\n",
       "  'a': 'Fujian',\n",
       "  'b': 'Xiamen',\n",
       "  'c': 1.25,\n",
       "  'd': 6},\n",
       " {'_id': ObjectId('595c528feaa295c58ad656ae'),\n",
       "  'a': 'Zhejiang',\n",
       "  'b': 'Hangzhou',\n",
       "  'c': 2.6,\n",
       "  'd': 3},\n",
       " {'_id': ObjectId('595c528feaa295c58ad656af'),\n",
       "  'a': 'Guangdong',\n",
       "  'b': 'Shenzhen',\n",
       "  'c': 1.7,\n",
       "  'd': 5}]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = db.data.find()\n",
    "list(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('595c528feaa295c58ad656ad'),\n",
       " 'a': 'Fujian',\n",
       " 'b': 'Xiamen',\n",
       " 'c': 1.25,\n",
       " 'd': 6}"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = db.data.find_one({'a': 'Fujian'})\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('595c528feaa295c58ad656ad'),\n",
       " 'a': 'Fujian',\n",
       " 'b': 'Fuzhou',\n",
       " 'c': 1.25,\n",
       " 'd': 6}"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.data.update_one({'a': 'Fujian'}, {'$set': {'b': 'Fuzhou'} })\n",
    "result = db.data.find_one({'a': 'Fujian'})\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('595c529e6b5c4ebf0f2fc646'),\n",
       " 'a': 'Shandong',\n",
       " 'b': 'Jinan',\n",
       " 'c': 2.25,\n",
       " 'd': 5}"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.data.update_one({'a': 'Shandong'}, {'$setOnInsert' :{'a': 'Shandong', 'b': 'Jinan', 'c': 2.25, 'd': 5}}, upsert = True )\n",
    "result = db.data.find_one({'a': 'Shandong'})\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': ObjectId('595c528feaa295c58ad656ad'),\n",
       "  'a': 'Fujian',\n",
       "  'b': 'Fuzhou',\n",
       "  'c': 1.25,\n",
       "  'd': 6},\n",
       " {'_id': ObjectId('595c528feaa295c58ad656ae'),\n",
       "  'a': 'Zhejiang',\n",
       "  'b': 'Hangzhou',\n",
       "  'c': 2.6,\n",
       "  'd': 3},\n",
       " {'_id': ObjectId('595c528feaa295c58ad656af'),\n",
       "  'a': 'Guangdong',\n",
       "  'b': 'Shenzhen',\n",
       "  'c': 1.7,\n",
       "  'd': 5},\n",
       " {'_id': ObjectId('595c529e6b5c4ebf0f2fc647'),\n",
       "  'a': 'Shandong',\n",
       "  'b': 'Jinan',\n",
       "  'c': 2.25}]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.data.delete_one({'a': 'Shandong'})\n",
    "db.data.update_one({'a': 'Shandong'}, {'$setOnInsert' :{'a': 'Shandong', 'b': 'Jinan', 'c': 2.25}}, upsert = True )\n",
    "results = db.data.find() #mongodb中collection中的文档结构不一定要一样\n",
    "list(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 练习：尝试连接mongodb数据库并操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、访问网络"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 1. http协议基础\n",
    "http（超文本传输协议）是基于 tcp/ip 协议的应用层协议。它不涉及数据包传输，主要规定了客户端和服务器之间的通信格式，默认使用80端口。\n",
    "https（超文本传输安全协议）经由http协议进行通信，但是利用ssl/tls加密数据包。\n",
    "#### url\n",
    "url(统一资源定位器)，用来唯一标识网络资源。例如：https://www.baidu.com/s?wd=python\n",
    "#### http请求和响应\n",
    "- http请求由三部分组成，分别是：请求行、消息报头、请求正文。常见的请求方式\n",
    "    - get\n",
    "    - post\n",
    "- http响应由三部分组成，分别是：状态行、消息报头、响应正文。常见的状态码有\n",
    "    - 200 OK\n",
    "    - 400 Bad Request\n",
    "    - 401 Unauthorized\n",
    "    - 403 Forbidden\n",
    "    - 404 Not Found\n",
    "    - 500 Internal Server Error\n",
    "    - 503 Server Unavailable\n",
    "- 使用开发人员工具"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 使用urllib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.baidu.com 200 OK\n",
      "<html>\r\n",
      "<head>\r\n",
      "\t<script>\r\n",
      "\t\tlocation.replace(location.href.replace(\"https://\",\"http://\"));\r\n",
      "\t</script>\r\n",
      "</head>\r\n",
      "<body>\r\n",
      "\t<noscript><meta http-equiv=\"refresh\" content=\"0;url=http://www.baidu.com/\"></noscript>\r\n",
      "</body>\r\n",
      "</html>\n"
     ]
    }
   ],
   "source": [
    "from urllib import request #python 2中是urllib2\n",
    "resp = request.urlopen('https://www.baidu.com')\n",
    "print(resp.geturl(), resp.status, resp.reason, sep=' ')\n",
    "print(resp.read().decode('utf-8'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status: 200 OK\n"
     ]
    }
   ],
   "source": [
    "resp = request.urlopen('https://api.douban.com/v2/movie/1292720')\n",
    "print('Status:', resp.status, resp.reason)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cache-Control': 'must-revalidate, no-cache, private',\n",
       " 'Connection': 'close',\n",
       " 'Content-Length': '2861',\n",
       " 'Content-Type': 'application/json; charset=utf-8',\n",
       " 'Date': 'Wed, 05 Jul 2017 02:44:48 GMT',\n",
       " 'Expires': 'Sun, 1 Jan 2006 01:00:00 GMT',\n",
       " 'Pragma': 'no-cache',\n",
       " 'Server': 'dae',\n",
       " 'Set-Cookie': 'bid=4-1j0zfK14s; Expires=Thu, 05-Jul-18 02:44:48 GMT; Domain=.douban.com; Path=/',\n",
       " 'Vary': 'Accept-Encoding',\n",
       " 'X-DAE-App': 'movie',\n",
       " 'X-DAE-Node': 'daisy6d',\n",
       " 'X-DOUBAN-NEWBID': '4-1j0zfK14s',\n",
       " 'X-Ratelimit-Limit2': '100',\n",
       " 'X-Ratelimit-Remaining2': '99'}"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "{k: v for k, v in resp.getheaders()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'alt': 'https://movie.douban.com/movie/1292720',\n",
       " 'alt_title': '阿甘正传 / 福雷斯特·冈普',\n",
       " 'attrs': {'cast': ['Tom Hanks',\n",
       "   'Robin Wright Penn',\n",
       "   'Gary Sinise',\n",
       "   'Mykelti Williamson',\n",
       "   'Sally Field',\n",
       "   'Michael Conner Humphreys',\n",
       "   'Haley Joel Osment'],\n",
       "  'country': ['美国'],\n",
       "  'director': ['Robert Zemeckis'],\n",
       "  'language': ['英语'],\n",
       "  'movie_duration': ['142 分钟'],\n",
       "  'movie_type': ['剧情', '爱情'],\n",
       "  'pubdate': ['1994-06-23(洛杉矶首映)', '1994-07-06(美国)'],\n",
       "  'title': ['Forrest Gump'],\n",
       "  'writer': ['Eric Roth', 'Winston Groom'],\n",
       "  'year': ['1994']},\n",
       " 'author': [{'name': 'Robert Zemeckis'}],\n",
       " 'id': 'https://api.douban.com/movie/1292720',\n",
       " 'image': 'https://img1.doubanio.com/view/movie_poster_cover/ipst/public/p510876377.jpg',\n",
       " 'mobile_link': 'https://m.douban.com/movie/subject/1292720/',\n",
       " 'rating': {'average': '9.4', 'max': 10, 'min': 0, 'numRaters': 689390},\n",
       " 'summary': '阿甘（汤姆·汉克斯 饰）于二战结束后不久出生在美国南方阿拉巴马州一个闭塞的小镇，他先天弱智，智商只有75，然而他的妈妈是一个性格坚强的女性，她常常鼓励阿甘“傻人有傻福”，要他自强不息。\\n阿甘像普通孩子一样上学，并且认识了一生的朋友和至爱珍妮（罗宾·莱特·潘 饰），在珍妮和妈妈的爱护下，阿甘凭着上帝赐予的“飞毛腿”开始了一生不停的奔跑。\\n阿甘成为橄榄球巨星、越战英雄、乒乓球外交使者、亿万富翁，但是，他始终忘不了珍妮，几次匆匆的相聚和离别，更是加深了阿甘的思念。\\n有一天，阿甘收到珍妮的信，他们终于又要见面……',\n",
       " 'tags': [{'count': 162555, 'name': '励志'},\n",
       "  {'count': 121782, 'name': '经典'},\n",
       "  {'count': 92368, 'name': '美国'},\n",
       "  {'count': 80775, 'name': '人生'},\n",
       "  {'count': 59557, 'name': '信念'},\n",
       "  {'count': 57160, 'name': '成长'},\n",
       "  {'count': 33062, 'name': '剧情'},\n",
       "  {'count': 23485, 'name': '人性'}],\n",
       " 'title': 'Forrest Gump'}"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "json.loads(resp.read().decode('utf-8'))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "from urllib import parse\n",
    "req = request.Request('https://accounts.douban.com/login')\n",
    "req.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) '\\\n",
    "               'AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36')\n",
    "payload = parse.urlencode([('form_email', 'username'), ('form_password', 'password'), ('redir', 'https://www.douban.com/people/username/')])\n",
    "with request.urlopen(req, data=payload.encode('utf-8')) as resp: #data是byte类型\n",
    "    print('Status:', resp.status, resp.reason)\n",
    "    #print(resp.read().decode('utf-8'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 使用requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.baidu.com/ 200 OK\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "resp = requests.get('https://www.baidu.com')\n",
    "print(resp.url, resp.status_code, resp.reason, sep=' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status: 200 OK\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'alt': 'https://movie.douban.com/movie/1292720',\n",
       " 'alt_title': '阿甘正传 / 福雷斯特·冈普',\n",
       " 'attrs': {'cast': ['Tom Hanks',\n",
       "   'Robin Wright Penn',\n",
       "   'Gary Sinise',\n",
       "   'Mykelti Williamson',\n",
       "   'Sally Field',\n",
       "   'Michael Conner Humphreys',\n",
       "   'Haley Joel Osment'],\n",
       "  'country': ['美国'],\n",
       "  'director': ['Robert Zemeckis'],\n",
       "  'language': ['英语'],\n",
       "  'movie_duration': ['142 分钟'],\n",
       "  'movie_type': ['剧情', '爱情'],\n",
       "  'pubdate': ['1994-06-23(洛杉矶首映)', '1994-07-06(美国)'],\n",
       "  'title': ['Forrest Gump'],\n",
       "  'writer': ['Eric Roth', 'Winston Groom'],\n",
       "  'year': ['1994']},\n",
       " 'author': [{'name': 'Robert Zemeckis'}],\n",
       " 'id': 'https://api.douban.com/movie/1292720',\n",
       " 'image': 'https://img1.doubanio.com/view/movie_poster_cover/ipst/public/p510876377.jpg',\n",
       " 'mobile_link': 'https://m.douban.com/movie/subject/1292720/',\n",
       " 'rating': {'average': '9.4', 'max': 10, 'min': 0, 'numRaters': 689390},\n",
       " 'summary': '阿甘（汤姆·汉克斯 饰）于二战结束后不久出生在美国南方阿拉巴马州一个闭塞的小镇，他先天弱智，智商只有75，然而他的妈妈是一个性格坚强的女性，她常常鼓励阿甘“傻人有傻福”，要他自强不息。\\n阿甘像普通孩子一样上学，并且认识了一生的朋友和至爱珍妮（罗宾·莱特·潘 饰），在珍妮和妈妈的爱护下，阿甘凭着上帝赐予的“飞毛腿”开始了一生不停的奔跑。\\n阿甘成为橄榄球巨星、越战英雄、乒乓球外交使者、亿万富翁，但是，他始终忘不了珍妮，几次匆匆的相聚和离别，更是加深了阿甘的思念。\\n有一天，阿甘收到珍妮的信，他们终于又要见面……',\n",
       " 'tags': [{'count': 162555, 'name': '励志'},\n",
       "  {'count': 121782, 'name': '经典'},\n",
       "  {'count': 92368, 'name': '美国'},\n",
       "  {'count': 80775, 'name': '人生'},\n",
       "  {'count': 59557, 'name': '信念'},\n",
       "  {'count': 57160, 'name': '成长'},\n",
       "  {'count': 33062, 'name': '剧情'},\n",
       "  {'count': 23485, 'name': '人性'}],\n",
       " 'title': 'Forrest Gump'}"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "resp = requests.get('https://api.douban.com/v2/movie/1292720')\n",
    "print('Status:', resp.status_code, resp.reason)\n",
    "json.loads(resp.text)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) '\\\n",
    "               'AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36'}\n",
    "payload = {'form_email': 'username', \n",
    "           'form_password': 'password', \n",
    "           'redir': 'https://www.douban.com/people/username/', \n",
    "           'captcha-solution': 'profit',\n",
    "           'captcha-id': 'EqNBhTFoh1MlvYVWv2qXo81I:en'}\n",
    "resp = requests.post('https://accounts.douban.com/login', data=payload, headers=headers)\n",
    "print('Status:', resp.status_code, resp.reason)\n",
    "#print(resp.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 使用pyspider\n",
    "一个简易的爬虫框架，支持多线程，js动态解析，带有web操作界面，支持多种数据库。windows中只python2下运行较为正常，ubuntu中支持python3。\n",
    "实例演示pyspider下爬虫开发\n",
    "http://docs.pyspider.org/en/latest/apis/self.crawl/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. 练习：尝试访问豆瓣api获取数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## 五、练习"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 1. 通过百度下载视频"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://www.baidu.com/link?url=QNlVhrRe-9pFVLlYnC1lUvQSYoDLCxN0DD406vIUpqPQpA_nGhnh-hPhP_XuaFF-9wc5T_KF37389iglBemivlPZiyLcsCopDWxtQUfm-Qq'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "import re\n",
    "from pyquery import PyQuery as pq\n",
    "\n",
    "key = '如何掌控你的自由时间'\n",
    "baiduUrl = 'https://www.baidu.com/s?wd=%s site:open.163.com'\n",
    "flvUrl = 'http://www.flvcd.com/parse.php?format=&kw=%s'\n",
    "headers = {\"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) '\\\n",
    "        'AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36\"}\n",
    "resp = requests.get(baiduUrl % key, headers=headers)\n",
    "page = pq(resp.text)\n",
    "items = [(item('a').attr('href'), item('div[.c-span-last]').text()) \n",
    "         for item in  page('div.c-container h3 a.anchor-link').items()]\n",
    "\n",
    "videoPageUrl = ''\n",
    "if(len(items) > 0):\n",
    "    firstResp = requests.get(items[0][0], allow_redirects=False, headers=headers)\n",
    "    if(firstResp.status_code == 302):\n",
    "        videoPageUrl = firstResp.headers.get('location')\n",
    "    elif(firstResp.status_code == 200):\n",
    "        matchGroup = re.search(r'URL=\\'(.*?)\\'', firstResp.text, re.S)\n",
    "        videoPageUrl = matchGroup.group(1)\n",
    "        print(videoPageUrl)\n",
    "    if videoPageUrl:\n",
    "        flvResp = requests.get(flvUrl % videoPageUrl, headers=headers)\n",
    "        flvPage = pq(flvResp.text)\n",
    "        flvItems = [item.attr('href') for item in flvPage('td.mn a.link').items()]\n",
    "        if(len(flvItems) > 0 and flvItems[0]):\n",
    "            videoUrl = flvItems[0]\n",
    "            print(videoUrl)\n",
    "            if videoUrl:\n",
    "                downResp = requests.get(videoUrl, stream=True)                \n",
    "                fileName = 'video/%s.flv' % key\n",
    "                with open(fileName, \"wb\") as file:\n",
    "                    file.write(downResp.content)\n",
    "                    print('finish')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 将上面的代码重构封装为类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import re\n",
    "from pyquery import PyQuery as pq\n",
    "\n",
    "class OnlineVideoCrawler(object):\n",
    "    def __init__(self, keys):\n",
    "        self.keys = keys\n",
    "        self.baiduUrl = 'https://www.baidu.com/s?wd=%s site:open.163.com'\n",
    "        self.flvUrl = 'http://www.flvcd.com/parse.php?format=&kw=%s'\n",
    "        self.headers = {\"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) '\\\n",
    "        'AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36\"}\n",
    "\n",
    "    def getVideoPageUrlFromBaidu(self, key):\n",
    "        resp = requests.get(self.baiduUrl % key, headers=self.headers)\n",
    "        page = pq(resp.text)\n",
    "        items = [(item('a').attr('href'), item('div.c-span-last').text()) for item in  page('div.c-container div.c-gap-top-small').items()]\n",
    "        videoPageUrl = ''\n",
    "        if(len(items) > 0):\n",
    "            firstResp = requests.get(items[0][0], allow_redirects=False, headers=self.headers)\n",
    "            if(firstResp.status_code == 302):\n",
    "                videoPageUrl = firstResp.headers.get('location')\n",
    "            elif(firstResp.status_code == 200):\n",
    "                matchGroup = re.search(r'URL=\\'(.*?)\\'', firstResp.text, re.S)\n",
    "                videoPageUrl = matchGroup.group(1)\n",
    "        print(videoPageUrl)\n",
    "        return videoPageUrl\n",
    "        \n",
    "    \n",
    "    def getVideoUrlFromFlv(self, key):\n",
    "        videoPageUrl = self.getVideoPageUrlFromBaidu(key)\n",
    "        videoUrl = ''\n",
    "        if videoPageUrl:\n",
    "            flvResp = requests.get(self.flvUrl % videoPageUrl, headers=self.headers)\n",
    "            flvPage = pq(flvResp.text)\n",
    "            flvItems = [item.attr('href') for item in flvPage('td.mn a.link').items()]\n",
    "            if(len(flvItems) > 0 and flvItems[0]):\n",
    "                videoUrl = flvItems[0]\n",
    "        print(videoUrl)\n",
    "        return videoUrl\n",
    "    \n",
    "    def downloadVideo(self, key):\n",
    "        videoUrl = self.getVideoUrlFromFlv(key)\n",
    "        downResp = requests.get(videoUrl, stream=True)\n",
    "        total = int(downResp.headers.get('content-length'))\n",
    "        print(total)\n",
    "        fileName = '%s.flv' % key\n",
    "        i = 0\n",
    "        chunk_size = 1024\n",
    "        with open(fileName, \"wb\") as file:\n",
    "            for chunk in downResp.iter_content(chunk_size=chunk_size):\n",
    "                if chunk:\n",
    "                    i += 1\n",
    "                    file.write(chunk)\n",
    "                    file.flush()\n",
    "                    print('%.2f' % (chunk_size * i / total), end='\\r')\n",
    "\n",
    "    def downloadVideos(self):\n",
    "        if len(self.keys) > 0:\n",
    "            for key in self.keys:\n",
    "                try:\n",
    "                    self.downloadVideo(key)\n",
    "                except Exception as e:\n",
    "                    print(e)\n",
    "\n",
    "#if __name__ == '__main__':\n",
    "keys = ['如何掌控你的自由时间','阅读全世界','如何做得更好']\n",
    "crawler = OnlineVideoCrawler(keys)\n",
    "crawler.downloadVideos()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 六、期末练习\n",
    "开发一个爬虫，要求任意选取站点（百度或豆瓣）获取站点的信息列表，要求文本数据50条以上，音频或视频不限。\n",
    "提交方式：将所有本章及本章前的小练习和本次期末练习代码，放置在一个notebook文件中，以 学号+姓名.ipynb 的方式命名，通过以下命令拷贝至公共路径。\n",
    "\n",
    "加深要求：\n",
    "\n",
    "1. 请使用GUI程序设计多界面（由于要能在Linux程序上跑，不能用Tkinter）\n",
    "可以使用wxPython或者PyQt或者Kivy等等。便于执行持续的循环\n",
    "\n",
    "2.请使用多线程并发爬取程序（注意界面下的线程安全问题）！\n",
    "\n",
    "3.文件读取请注意"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "!cp ~/学号+姓名.ipynb  /mnt/python/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "pycharm-5941a300",
   "language": "python",
   "display_name": "PyCharm (pythonProject)"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}